package wty;

import java.sql.*;
import javax.swing.table.DefaultTableModel;

public class Administrator_SQL {

    public static DefaultTableModel SQL() {
        //DefaultTableModel是一个
        DefaultTableModel model = new DefaultTableModel();
        model.addColumn("Name");
        model.addColumn("ID");
        model.addColumn("Password");
        model.addColumn("PhoneNum");

        try {
            com.mysql.cj.jdbc.Driver mysqlDriver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(mysqlDriver);
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("未能正常加载MySQL驱动");
        }

        String url = "jdbc:mysql://localhost:3306/project";
        String username = "root";
        String password = "root";
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(url, username, password);
            String sql = "SELECT * FROM users";
            Statement sqlStatement = connection.createStatement();
            ResultSet resultSet = sqlStatement.executeQuery(sql);

            while (resultSet.next()) {
                String name = resultSet.getString("Name");
                String id = resultSet.getString("ID");
                String passwordValue = resultSet.getString("Password");
                String phoneNum = resultSet.getString("PhoneNum");
                model.addRow(new Object[]{name, id, passwordValue, phoneNum});
            }

            resultSet.close();
            sqlStatement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.err.println("数据库在连接期间，出现错误！");
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                System.err.println("在执行数据库关闭动作时，出现错误！");
            }
        }
        return model;
    }

    public static void updateUserPassword(String userId, String newPassword) {
        String url = "jdbc:mysql://localhost:3306/project";
        String username = "root";
        String password = "root";
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(url, username, password);
            String sql = "UPDATE users SET Password = ? WHERE ID = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setString(1, newPassword);
                preparedStatement.setString(2, userId);
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("更新密码时出现错误！");
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                System.err.println("在执行数据库关闭动作时，出现错误！");
            }
        }
    }
}
